﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using VOL;
using DAL;

namespace BLL
{
    public class HoaDonBL
    {
        private DataAccess dataAccess;
        public HoaDonBL()
        {
            dataAccess = new DataAccess();
        }

        public DataTable GetData()
        {
            string strSql = "SELECT * FROM tbl_HoaDon ORDER BY MaHoaDon DESC ";
            return dataAccess.GetData(strSql);
        }

        //Thêm
        public bool insertHoaDon(HoaDon obj)
        {
            SqlParameter[] param = new SqlParameter[6];
            param[0] = new SqlParameter("@NgayMua", SqlDbType.DateTime) { Value = obj.NgayMua };
            param[1] = new SqlParameter("@SoLuong", SqlDbType.Int) { Value = obj.SoLuong };
            param[2] = new SqlParameter("@MaKhachHang", SqlDbType.Int) { Value = obj.MaKhachHang };
            param[3] = new SqlParameter("@MaHangHoa", SqlDbType.Int) { Value = obj.MaHangHoa };
            param[4] = new SqlParameter("@DonGia", SqlDbType.Float) { Value = obj.DonGia };
            param[5] = new SqlParameter("@NgayGiaoHang", SqlDbType.DateTime) { Value = obj.NgayGiaoHang };


            string strSql = "INSERT INTO tbl_HoaDon(MaKhachHang,MaHangHoa,DonGia,SoLuong,NgayMua,NgayGiaoHang) VALUES (@MaKhachHang,@MaHangHoa,@DonGia,@SoLuong,@NgayMua,@NgayGiaoHang)";
            dataAccess.Execute(strSql, param);

            return true;
        }

        ////Xóa
        public bool deleteHoaDon(HoaDon obj)
        {
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@MaHoaDon", SqlDbType.Int) { Value = obj.MaHoaDon };

            string strSql = "DELETE FROM tbl_HoaDon WHERE MaHoaDon = @MaHoaDon";
            dataAccess.Execute(strSql, param);
            return true;
        }

        ////Sửa
        public bool updateHoaDon(HoaDon obj)
        {
            SqlParameter[] param = new SqlParameter[8];
            param[0] = new SqlParameter("@NgayMua", SqlDbType.DateTime) { Value = obj.NgayMua };
            param[1] = new SqlParameter("@SoLuong", SqlDbType.Int) { Value = obj.SoLuong };
            param[2] = new SqlParameter("@MaKhachHang", SqlDbType.Int) { Value = obj.MaKhachHang };
            param[3] = new SqlParameter("@MaHangHoa", SqlDbType.Int) { Value = obj.MaHangHoa };
            param[4] = new SqlParameter("@DonGia", SqlDbType.Float) { Value = obj.DonGia };
            param[5] = new SqlParameter("@MaNguoiQuanLy", SqlDbType.Int) { Value = obj.MaNguoiQuanLy };
            param[6] = new SqlParameter("@MaHoaDon", SqlDbType.Int) { Value = obj.MaHoaDon };
            param[7] = new SqlParameter("@NgayGiaoHang", SqlDbType.DateTime) { Value = obj.NgayGiaoHang };

            string strSql = "UPDATE tbl_HoaDon SET " +
                            "NgayMua = @NgayMua, " +
                            "SoLuong = @SoLuong, " +
                            "MaKhachHang = @MaKhachHang, " +
                            "MaHangHoa = @MaHangHoa, " +
                            "DonGia = @DonGia, " +
                            "NgayGiaoHang = @NgayGiaoHang, "+
                            "MaNguoiQuanLy = @MaNguoiQuanLy " +
                            "WHERE MaHoaDon = @MaHoaDon";
            dataAccess.Execute(strSql, param);
            return true;
        }

        public void updateNgayGiaoHang(int maHoaDon,DateTime ngayGiaoHang)
        {
            string strSql = "UPDATE tbl_HoaDon SET NgayGiaoHang = '" + ngayGiaoHang.ToString() + "' WHERE MaHoaDon = " + maHoaDon.ToString();
            dataAccess.Execute(strSql);
        }

        ////Tìm theo Mã Khách Hàng
        public DataTable searchIDHoaDon(int maHoaDon)
        {
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@MaHoaDon", SqlDbType.Int) { Value = maHoaDon };

            string strSql = "SELECT * FROM tbl_HoaDon WHERE MaHoaDon = @MaHoaDon";
            return dataAccess.GetData(strSql, param);
        }

        public DataTable viewHoaDon()
        {
            string strSql = "SELECT tbl_HoaDon.MaHoaDon, tbl_HoaDon.MaKhachHang, tbl_HoaDon.NgayMua, tbl_HoaDon.SoLuong, tbl_HoaDon.DonGia, tbl_HoaDon.MaNguoiQuanLy, tbl_HoaDon.NgayGiaoHang, tbl_KhachHang.HoTenKhachHang, tbl_KhachHang.DiaChi, tbl_KhachHang.DienThoai, tbl_KhachHang.Email, tbl_KhachHang.DienThoaiDiDong FROM tbl_HoaDon INNER JOIN tbl_KhachHang ON tbl_HoaDon.MaKhachHang = tbl_KhachHang.MaKhachHang ORDER BY tbl_HoaDon.MaHoaDon DESC";
            return dataAccess.GetData(strSql);
        }

        public DataTable searchMaHoaDon(int maHoaDon)
        {
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@MaHoaDon", SqlDbType.Int) { Value = maHoaDon };

            string strSql = "SELECT tbl_HoaDon.MaHoaDon, tbl_HoaDon.MaKhachHang, tbl_HoaDon.NgayMua, tbl_HoaDon.SoLuong, tbl_HoaDon.DonGia, tbl_HoaDon.MaNguoiQuanLy, tbl_HoaDon.NgayGiaoHang, tbl_KhachHang.HoTenKhachHang, tbl_KhachHang.DiaChi, tbl_KhachHang.DienThoai, tbl_KhachHang.Email, tbl_KhachHang.DienThoaiDiDong FROM tbl_HoaDon INNER JOIN tbl_KhachHang ON tbl_HoaDon.MaKhachHang = tbl_KhachHang.MaKhachHang WHERE tbl_HoaDon.MaHoaDon = @MaHoaDon ORDER BY MaHoaDon DESC";
            return dataAccess.GetData(strSql, param);
        }
        
    }
}
